package packageGenerateData;

import packageInputData.DbConnection;
import packageInputData.ReadInputData;

import java.sql.Connection;
import java.sql.Date;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ArrayListHandler;

public class GenerateDataSPOK {

	
	static int paramEpokDeb= 	(Integer)(DbConnection.getFromDB("epok_deb", "param"));
	static int paramEpokEnd= 	(Integer)(DbConnection.getFromDB("epok_fin", "param"));
	static int paramEpokInc= 	(Integer)(DbConnection.getFromDB("epok_inc", "param"));
	
	
	/**
	 * function 
	 * to generate Epok data and put it into the database
	 *  @return			void
	*/
	@SuppressWarnings({ "deprecation", "rawtypes" })
	public static void generateEpok()
	{
		int doy=0,year=0,inserts=0;
		int slantDelayId=0,epok=0,epokTime=0;
		double time =0, sta, sat, elev, azimu, slant_td, tt=0;
		Date date=null;
		Timestamp tmstamp =null;
		
		//connect to the database
		Connection conn=DbConnection.connectDB();					  			
	  	QueryRunner qRunner = new QueryRunner();
		List list1 = new ArrayList();
		
		//get the date, the year and the doy (day of the year in jullian calendar)
		doy=(Integer)DbConnection.getFromDB("doy","param");
		date=(Date)DbConnection.getFromDB("date","param");
		year=(date.getYear()+1900);
		
		int paramEpokDeb= 	(Integer)(DbConnection.getFromDB("epok_deb", "param"));
		int paramEpokEnd= 	(Integer)(DbConnection.getFromDB("epok_fin", "param"));
		int paramEpokInc= 	(Integer)(DbConnection.getFromDB("epok_inc", "param"));
		
		
		Integer i=0;
		for (i=0;i<24;i++)
		{ 
			
			//get id list from table slant_delay with the requested timestamp
			tmstamp=ReadInputData.convertToTimestamp(year,doy,i.toString());
			try {
			       list1 =(List) qRunner.query(conn,
								    		   "select id from slant_delay where tmstamp=?",
								    		   tmstamp,
								    		   new ArrayListHandler());
			} catch (SQLException e2) {
			       e2.printStackTrace();
			}
			//read the result list of id and take the last one
			Object data[] = (Object[]) list1.get(list1.size()-1);
			if(data.length>0)
			{
				slantDelayId=Integer.parseInt(data[0].toString());
			}
			double timeRef=0;
			
			//get data from slat_data for the specified id "slantDelayId"
			String req= "select time, sta, sat, elev, azimu, slant_td  from slant_data where slant_id=?";
			try {
				list1 =(List) qRunner.query(conn,
											req,
											slantDelayId,
											new ArrayListHandler());
				} catch (SQLException e2) {
				e2.printStackTrace();
				}
			//read data
			Object res[]=null;
			if(list1.size()>0)
			{
					 res = (Object[]) list1.get(0);	
					 timeRef=Double.parseDouble(res[0].toString());												 
			}
			
			//prepare query to insert into table "epok"
			req="INSERT INTO epok" +
					"(session_id,epok,sta,sta_name," +
					"sat,el,az,sl,epok_time)" +				
					"VALUES (?,?,?,?,?,?,?,?,?)";
			
			for (epok=0;epok<=90;epok=epok+30)
			{
				epokTime=epok+120*i;
				if((paramEpokDeb <= epokTime)&&(epokTime<=paramEpokEnd))
					{				
					for (int cpt=0;cpt<list1.size();cpt++)
					{
						 res = (Object[]) list1.get(cpt);
						 
						 time=		Double.parseDouble(res[0].toString());
						 sta= 		Double.parseDouble(res[1].toString())/100;
						 sat= 		Double.parseDouble(res[2].toString());
						 elev= 		Double.parseDouble(res[3].toString());
						 azimu= 	Double.parseDouble(res[4].toString());
						 slant_td=	Double.parseDouble(res[5].toString());
						 
						 if (azimu<0)
							 azimu=azimu+360;
						 
						 if (time>(timeRef-0.000005) && time <(timeRef+0.000005))
						 {
							 tt=time;	
									 try {
										inserts = qRunner.update(conn,req,
													MainClass.session,
													epok,
													(int)sta,
													DbConnection.getCodeStationByID((int)sta).toUpperCase(),
													(int)sat,
													elev,
													azimu,
													slant_td,
													epokTime);
									
											} catch (SQLException e) {									
												e.printStackTrace();
											}							  			
						 }
					}							
					
					//update "timeRef" for the next for iteration
					timeRef=tt+2*0.001737;
					//test if the insertion is done correctly
					if (inserts==1)
					{
		  				System.out.println("Insertion in epok "+epokTime );
		  				inserts=0;
					}
				
				}
			}
		
			
		//end for (0->24)
		}
		
		//close connection
		DbUtils.closeQuietly(conn);
	}
	/**
	 * function 
	 * to generate Vcopsepok data and put it into the database
	 *  @return			void
	*/
	@SuppressWarnings({ "deprecation", "rawtypes" })
	public static void generateVcopsepok()
	{
		
		List list1 = new ArrayList();
		List list2 = new ArrayList();
		String req = "";
		int epok=0,inserts=0;
	
		//connect to the database
		Connection conn=DbConnection.connectDB();					  			
	  	QueryRunner qRunner = new QueryRunner();
		
		
		//get GPS code list from sites_gps in list1 then in data[]
		try {
		       list1 =(List) qRunner.query(conn,
							    		   "select code from sites_gps",
							    		   new ArrayListHandler());
		} catch (SQLException e2) {
		       e2.printStackTrace();
		}
		
		for (epok=paramEpokDeb;epok<=paramEpokEnd;epok=epok+paramEpokInc)
		{							
			for(int j=0;j<list1.size();j++)
			{
				
				//get epok id  list from epok in list2 then in data1[]
				Object data[] = (Object[]) list1.get(j);
				//prepare query
				req="select id from epok where sta_name=? and epok_time="+
					epok+" and session_id= "+
					MainClass.session;
				//execute query
				try {
				       list2 =(List) qRunner.query(conn,
									    		   req,
									    		   data[0].toString(),
									    		   new ArrayListHandler());
				} catch (SQLException e2) {
				       e2.printStackTrace();
				}
				//get the results of the query
				for(int k=0;k<list2.size();k++)
				{
					//test if the results existed
					if (list2.size()>0)
					{
						Object data1[] = (Object[]) list2.get(k);
						//prepare query
						req="INSERT INTO vcopsepok" +
								"(session_id,sta_name," +
								"epok_time,epok_id)" +				
								"VALUES (?,?,?,?)";
						//execute query
						 try {
								inserts = qRunner.update(conn,req,
											MainClass.session,
											data[0].toString(),
											epok,
											Integer.parseInt(data1[0].toString()));
							
							} catch (SQLException e) {									
								e.printStackTrace();
							}						 
					}
				}
				//end of "for" (code GPS list)
			}
			//test if the insertion is done correctly
			 if (inserts==1)
				{
	  				System.out.println("Insertion in vecopspok for epok = "+epok +" done ");
	  				inserts=0;
				}
		}
		//close connection
		DbUtils.closeQuietly(conn);
	}	
}
